<?php
require_once 'PhpExcel/PHPExcel.php';
require_once 'PhpExcel/PHPExcel/IOFactory.php';
require_once 'PhpExcel/PHPExcel/Reader/Excel5.php';

$con = new mysqli("localhost", "root", "", "excel");
mysqli_query($con,"set names 'utf8'"); //数据库输出编码

//上传excel
if(isset($_POST['submit']) && !empty($_POST['submit']))
{
    if($_FILES['img']['error'] != 4)
    {
        $uploadRoot = './upload/';//设置上传目录
        $files = $_FILES['img'];
        $fileName = $files['name'];
        //判断是否对上传目录拥有写权限
        if(!is_writable($uploadRoot))
        {
            echo '没有对上传目录的写权限';
            exit();
        }

        //生成上传文件在上传目录中的文件名
        $names = explode('.', $fileName);
        $newname = time().'.'.end($names);

        $fileSaved = $uploadRoot.$newname;
        if(!move_uploaded_file($files['tmp_name'], $fileSaved))
        {
            echo '移动文件失败';
            exit();
        }
    }
}

//操作excel
    //加载类
    /** 用PHPExcel_IOFactory的load方法得到excel操作对象  **/
    $objPHPExcel = PHPExcel_IOFactory::load('./upload/'.$newname);
    
    //得到当前活动表格，调用toArray方法，得到表格的二维数组
    $sheetData =$objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
    
    //判断第一行格式是否正确
    $data_one = implode(',', array_filter($sheetData[1]));
    if( $data_one == '省份,市,销量' ){
    	foreach(array_filter($sheetData) as $key => $v){
    		if($key > 1 ){
                if( is_null($v['A']) ){$dh_kf = '';}else{$A = $v['A']; }
                if( is_null($v['B']) ){$B = '';}else{$B = $v['B']; }
                if( is_null($v['C']) ){$C = '';}else{$C = $v['C']; }
                if( $B!='' && $A!=''){
                    $val_sql = array(
                        'prov'	=> $A,
                        'city'=> $B,
                        'sales' => $C,
                    );
                    $select = "select * from test where prov = '".$A."' and city = '".$B."'";
                    $query = $con->query($select);
                    if($query->num_rows == 0){
                        $sql = "insert into test (prov,city,sales) values ('".$A."','".$B."','".$C."')";
                        $query = $con->query($sql);
                    }else{
                        $update = "update test set sales = '".$C."' where prov = '".$A."' and city = '".$B."'";
                        $query = $con->query($update);
                    }
                }
    		}
    	}
    mysqli_close(); //关闭MySQL连接
    }else{
    	echo '请检查文件格式是否正确';
    	exit;
    } 
    rmdir('./upload/'.$newname);
    header("Location: MyECharts/index.php");
?>


    
